{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "785772b7-7003-4b16-9409-c041d7dabe57",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from alive_progress import alive_bar\n",
    "import xlwings as xw\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dcc4996e-9a76-424c-8210-87ccdd099c01",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_excel('./效率_月累计.xlsx',sheet_name='Sheet1')\n",
    "target_file = './结果表.xlsx'\n",
    "\n",
    "df = df.apply(pd.to_numeric,errors='ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e6ea1dbb-9d9a-4c25-8d2b-db9fea8c3105",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['播放开始小时']= df['播放开始小时'].astype(int)\n",
    "df['播放日期'] = pd.to_datetime(df['播放日期'])\n",
    "df = df[ ( df['md名'] != '房产服务') & ( df['md名'] != '全装修工程') & ( df['md名'] != '汽车整车') & ( df['md名'] != '寿险')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a7117887-163e-407d-b560-4ed133970c27",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[['播放日期','频道','On-Air金额','On-Air利润','希望播放（分）']]\n",
    "df['频道'] = df['频道'].str.replace('频道','')\n",
    "df['频道'] = df['频道'].astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "ae9d12c8-228c-45c8-9f08-cad963cb79a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_group = df.groupby(['播放日期','频道'])['On-Air金额','On-Air利润','希望播放（分）'].sum().reset_index()\n",
    "df_group = df_group[df_group['播放日期']==max(df_group['播放日期'])]\n",
    "df_sum = df_group.sum()\n",
    "df_sum['播放日期'] = np.nan\n",
    "df_sum['频道'] = np.nan\n",
    "df_sum = pd.DataFrame(df_sum[['播放日期','频道','On-Air金额','On-Air利润','希望播放（分）']]).T\n",
    "df_result = pd.concat([df_group ,df_sum])\n",
    "df_result['分钟订购'] = (df_result['On-Air金额'] / df_result['希望播放（分）']).round(0)\n",
    "df_result['分钟利润'] = (df_result['On-Air利润'] / df_result['希望播放（分）']).round(0)\n",
    "df_result['频道'] = df_result['频道'].astype('float')\n",
    "df_result['播放日期'] = pd.to_datetime(df_result['播放日期'],format='%Y=%m-%d')\n",
    "df_result['播放日期'] = df_result['播放日期'].astype('str')[:10]\n",
    "df_result = df_result[['播放日期','频道','分钟订购','分钟利润']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "52350839-867d-4cea-b906-b045164f52f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_group2 = df.groupby(['频道'])['On-Air金额','On-Air利润','希望播放（分）'].sum().reset_index()\n",
    "df_sum2 = df_group2.sum()\n",
    "df_sum2['频道'] = np.nan\n",
    "df_sum2 = pd.DataFrame(df_sum2[['频道','On-Air金额','On-Air利润','希望播放（分）']]).T\n",
    "df_result2 = pd.concat([df_group2 ,df_sum2])\n",
    "df_result2['分钟订购'] = (df_result2['On-Air金额'] / df_result2['希望播放（分）']).round(0)\n",
    "df_result2['分钟利润'] = (df_result2['On-Air利润'] / df_result2['希望播放（分）']).round(0)\n",
    "df_result2['频道'] = df_result2['频道'].astype('float')\n",
    "df_result2 = df_result2[['频道','分钟订购','分钟利润']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "868099d2-390e-45c2-89c7-3e229a779017",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter(target_file) as writer:\n",
    "    df_result.to_excel(writer,sheet_name='当天', index=False)\n",
    "    df_result2.to_excel(writer,sheet_name='月累计', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0a28bfc7-c918-4503-9bbb-561337e718e5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "|████████████████████████████████████████| 2/2 [100%] in 1.0s (1.98/s) \n",
      "\n",
      "表格输出完成！！\n"
     ]
    }
   ],
   "source": [
    "app = xw.App(visible=False,add_book=False)\n",
    "workbook = app.books.open('./结果表.xlsx')\n",
    "\n",
    "with alive_bar(len(workbook.sheets)) as bar:    \n",
    "    for i in workbook.sheets:\n",
    "        bar()\n",
    "        # pbar.set_description(f'Processing {i}')\n",
    "    # 批量设置格式（行高、列宽、字体、大小、线框）\n",
    "        a4_range=i.range('A4')\n",
    "        a4_range.value='合计'\n",
    "        value = i.range('A1').expand('table')# 选择要调整的区域\n",
    "        value.rows.autofit() # 调整列宽字符宽度\n",
    "        value.columns.autofit()  # 调整行高字符宽度\n",
    "        value.api.Font.Name = '微软雅黑' # 设置字体\n",
    "        value.api.Font.Size = 9 # 设置字号大小（磅数）\n",
    "        value.api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter # 设置垂直居中\n",
    "        value.api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter # 设置水平居中\n",
    "        for cell in value:\n",
    "            for b in range(7,12):\n",
    "                cell.api.Borders(b).LineStyle = 1 # 设置单元格边框线型\n",
    "                cell.api.Borders(b).Weight = 2 # 设置单元格边框粗细\n",
    "        value = i.range('A1').expand('right')  # 选择要调整的区域\n",
    "        value.api.Font.Size = 10\n",
    "        value.api.Font.Bold = True  # 设置为粗体\n",
    "        # print(f'《{i.name}》 页面处理完成……')\n",
    "workbook.save()\n",
    "workbook.close()\n",
    "app.quit()\n",
    "\n",
    "print(f'\\n表格输出完成！！')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
